package com.yehaikeji.workflow.common.util;

import java.awt.Graphics2D;
import java.awt.Image;
import java.awt.RenderingHints;
import java.awt.geom.AffineTransform;
import java.awt.image.AffineTransformOp;
import java.awt.image.BufferedImage;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.Iterator;
import java.util.List;

import javax.imageio.ImageIO;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFPatriarch;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;

import com.github.pagehelper.util.StringUtil;
import com.yehaikeji.workflow.bean.Excelthing;
import com.yehaikeji.workflow.bean.incident;
import com.yehaikeji.workflow.bean.thing;

public class ExcelUtil {

	private static final String[] thingHead = { "事项名", "群众名", "联系电话", "数量", "办理类型", "办理状态", "交件时间", "交件社区", "中转部门",
			"交件人", "中转部门接件人", "交件人签名", "接件人签名", "中转时间", "办理部门", "办理时限", "中转部门交件人", "办理部门接件人", "交件人签名", "接件人签名" };
	private static final String[] thingHead2 = { "事项名", "群众名", "联系电话", "数量", "办理类型", "办理状态", "交件时间", "交件社区", "中转部门",
			"交件人", "中转部门接件人", "交件人签名", "接件人签名", "中转时间", "办理部门", "办理时限", "中转部门交件人", "办理部门接件人", "交件人签名", "接件人签名", "办结时间",
			"办结部门办结人", "办结人签名" };

	private static final String[] thingHead3 = { "事项名", "群众名", "联系电话", "数量", "办理类型", "办理状态", "交件时间", "交件社区", "中转部门",
			"交件人", "中转部门接件人", "交件人签名", "接件人签名", "中转时间", "办理部门", "办理时限", "中转部门交件人", "办理部门接件人", "交件人签名", "接件人签名", "返件时间",
			"办理部门返件人", "中转部门接件人", "交件人签名", "接件人签名", "中转时间", "中转部门发件人", "社区接件人", "交件人签名", "接件人签名" };
	private static final String[] thingHead4 = { "事项名", "群众名", "联系电话", "数量", "办理类型", "办理状态", "交件时间", "交件社区", "中转部门",
			"交件人", "中转部门接件人", "交件人签名", "接件人签名", "中转时间", "办理部门", "办理时限", "中转部门交件人", "办理部门接件人", "交件人签名", "接件人签名", "退件时间",
			"退件原因", "办理部门退件人", "中转部门接件人", "交件人签名", "接件人签名", "中转时间", "中转部门发件人", "社区接件人", "交件人签名", "接件人签名" };

	private static final String[] thingHead5 = { "事项名", "数量", "办理类型", "办理状态", "交件时间", "交件社区", "中转部门", "交件人", "中转部门接件人",
			"交件人签名", "接件人签名", "中转时间", "接件部门", "中转部门交件人", "接件部门接件人", "交件人签名", "接件人签名" };
	private static final String[] thingHead6 = { "事项名", "数量", "办理类型", "办理状态", "交件时间", "交件部门", "中转部门", "交件人", "中转部门接件人",
			"交件人签名", "接件人签名", "中转时间", "接件社区", "中转部门交件人", "接件社区接件人", "交件人签名", "接件人签名" };

	/**
	 * list转换成excel
	 * 
	 * @param dataList
	 * @return
	 * @throws IOException
	 */
	@SuppressWarnings("deprecation")
	public static HSSFWorkbook dataToExcel(List<Excelthing> dataList, String url) throws IOException {
		HSSFWorkbook workbook = new HSSFWorkbook();

		int ajtitleLen = thingHead.length;
		int ajtitleLen2 = thingHead2.length;
		int ajtitleLen3 = thingHead3.length;
		int ajtitleLen4 = thingHead4.length;
		int ajtitleLen5 = thingHead5.length;
		int ajtitleLen6 = thingHead6.length;
		HSSFSheet xsSheet = workbook.createSheet("代办等待");
		xsSheet.setDefaultColumnWidth(30);
		xsSheet.setDefaultRowHeightInPoints(60);
		// 声明一个画图的顶级管理器
		HSSFPatriarch patriarch = xsSheet.createDrawingPatriarch();

		HSSFSheet xsSheet2 = workbook.createSheet("代办办结");
		xsSheet2.setDefaultColumnWidth(30);
		xsSheet2.setDefaultRowHeightInPoints(60);
		// 声明一个画图的顶级管理器
		HSSFPatriarch patriarch2 = xsSheet2.createDrawingPatriarch();

		HSSFSheet xsSheet3 = workbook.createSheet("代办返件");
		xsSheet3.setDefaultColumnWidth(30);
		xsSheet3.setDefaultRowHeightInPoints(60);
		// 声明一个画图的顶级管理器
		HSSFPatriarch patriarch3 = xsSheet3.createDrawingPatriarch();

		HSSFSheet xsSheet4 = workbook.createSheet("代办退件");
		xsSheet4.setDefaultColumnWidth(30);
		xsSheet4.setDefaultRowHeightInPoints(60);
		// 声明一个画图的顶级管理器
		HSSFPatriarch patriarch4 = xsSheet4.createDrawingPatriarch();

		HSSFSheet xsSheet5 = workbook.createSheet("代收");
		xsSheet5.setDefaultColumnWidth(30);
		xsSheet5.setDefaultRowHeightInPoints(60);
		// 声明一个画图的顶级管理器
		HSSFPatriarch patriarch5 = xsSheet5.createDrawingPatriarch();

		HSSFSheet xsSheet6 = workbook.createSheet("代发");
		xsSheet6.setDefaultColumnWidth(30);
		xsSheet6.setDefaultRowHeightInPoints(60);
		// 声明一个画图的顶级管理器
		HSSFPatriarch patriarch6 = xsSheet6.createDrawingPatriarch();
		// 设置表头和样式
		HSSFRow xsRow = xsSheet.createRow((short) 0);
		for (int i = 0; i < ajtitleLen; i++) {
			HSSFCell cell = xsRow.createCell((short) i);
			cell.setCellStyle(createHeadStyle(workbook));
			cell.setCellValue(thingHead[i]);
		}
		HSSFRow xsRow2 = xsSheet2.createRow((short) 0);
		for (int i = 0; i < ajtitleLen2; i++) {
			HSSFCell cell = xsRow2.createCell((short) i);
			cell.setCellStyle(createHeadStyle(workbook));
			cell.setCellValue(thingHead2[i]);
		}
		HSSFRow xsRow3 = xsSheet3.createRow((short) 0);
		for (int i = 0; i < ajtitleLen3; i++) {
			HSSFCell cell = xsRow3.createCell((short) i);
			cell.setCellStyle(createHeadStyle(workbook));
			cell.setCellValue(thingHead3[i]);
		}
		HSSFRow xsRow4 = xsSheet4.createRow((short) 0);
		for (int i = 0; i < ajtitleLen4; i++) {
			HSSFCell cell = xsRow4.createCell((short) i);
			cell.setCellStyle(createHeadStyle(workbook));
			cell.setCellValue(thingHead4[i]);
		}
		HSSFRow xsRow5 = xsSheet5.createRow((short) 0);
		for (int i = 0; i < ajtitleLen5; i++) {
			HSSFCell cell = xsRow5.createCell((short) i);
			cell.setCellStyle(createHeadStyle(workbook));
			cell.setCellValue(thingHead5[i]);
		}
		HSSFRow xsRow6 = xsSheet6.createRow((short) 0);
		for (int i = 0; i < ajtitleLen6; i++) {
			HSSFCell cell = xsRow6.createCell((short) i);
			cell.setCellStyle(createHeadStyle(workbook));
			cell.setCellValue(thingHead6[i]);
		}

		// 单元格样式
		HSSFCellStyle bodyStyle = createBodyStyle(workbook);

		Iterator<Excelthing> it = dataList.iterator();
		int j = 0, a = 0, b = 0, c = 0, d = 0, e = 0;
		SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm");

		while (it.hasNext()) {
			Excelthing excelthing = it.next();
			thing thing = excelthing.getThing();
			List<incident> list = excelthing.getList();

			if (thing.getTypeid() == 1 || thing.getTypeid() == 4) {
				if (thing.getStatus() < 3) {
					HSSFRow datarow = xsSheet.createRow(j + 1);
					HSSFCell data[] = new HSSFCell[ajtitleLen];
					for (short k = 0; k < ajtitleLen; k++) {
						data[k] = datarow.createCell(k);
						data[k].setCellStyle(bodyStyle);
					}
					data[0].setCellValue(thing.getName());
					data[1].setCellValue(thing.getBname());
					data[2].setCellValue(thing.getBphone());
					data[3].setCellValue(String.valueOf(thing.getNumber()));
					data[4].setCellValue(getTypeName(thing));
					data[5].setCellValue(getStatusName(thing));

					incident incident = null;
					if ((list.size() - 1) >= 0) {
						incident = list.get(list.size() - 1);
						data[6].setCellValue(dateFormat.format(incident.getCreatedate()));
						data[7].setCellValue(incident.getSendunit());
						data[8].setCellValue(incident.getAccepetunit());
						data[9].setCellValue(incident.getSendname());
						data[10].setCellValue(incident.getAcceptname());
						String sString = incident.getSignatures();
						String aString = incident.getSignaturea();
						if (!StringUtil.isEmpty(sString)) {
							sString = url + sString.substring(sString.lastIndexOf("/"));
							File file = new File(sString);
							if (file.exists()) {
								insertImage(workbook, patriarch, getImageData(ImageIO.read(file)), j + 1, 11, 1);
							} else {
								data[11].setCellValue("");
							}
						} else {
							data[11].setCellValue("");
						}
						if (!StringUtil.isEmpty(aString)) {
							aString = url + aString.substring(aString.lastIndexOf("/"));
							File file = new File(aString);
							if (file.exists()) {
								insertImage(workbook, patriarch, getImageData(ImageIO.read(file)), j + 1, 12, 1);
							} else {
								data[12].setCellValue("");
							}

						} else {
							data[12].setCellValue("");
						}

					}

					if ((list.size() - 2) >= 0) {
						incident = list.get(list.size() - 2);
						data[13].setCellValue(dateFormat.format(incident.getCreatedate()));
						data[14].setCellValue(incident.getAccepetunit());

						if (incident.getTime() == null) {
							data[15].setCellValue("");
						} else {
							data[15].setCellValue(incident.getTime());
						}

						data[16].setCellValue(incident.getSendname());
						data[17].setCellValue(incident.getAcceptname());
						String sString = incident.getSignatures();
						String aString = incident.getSignaturea();
						if (!StringUtil.isEmpty(sString)) {
							sString = url + sString.substring(sString.lastIndexOf("/"));
							File file = new File(sString);
							if (file.exists()) {
								insertImage(workbook, patriarch, getImageData(ImageIO.read(file)), j + 1, 18, 1);
							} else {
								data[18].setCellValue("");
							}
						} else {
							data[18].setCellValue("");
						}
						if (!StringUtil.isEmpty(aString)) {
							aString = url + aString.substring(aString.lastIndexOf("/"));
							File file = new File(aString);
							if (file.exists()) {
								insertImage(workbook, patriarch, getImageData(ImageIO.read(file)), j + 1, 19, 1);
							} else {
								data[19].setCellValue("");
							}

						} else {
							data[19].setCellValue("");
						}
					}
					j++;
				} else if (thing.getStatus() == 3) {
					HSSFRow datarow = xsSheet2.createRow(a + 1);
					HSSFCell data[] = new HSSFCell[ajtitleLen2];
					for (short k = 0; k < ajtitleLen2; k++) {
						data[k] = datarow.createCell(k);
						data[k].setCellStyle(bodyStyle);
					}
					data[0].setCellValue(thing.getName());
					data[1].setCellValue(thing.getBname());
					data[2].setCellValue(thing.getBphone());
					data[3].setCellValue(String.valueOf(thing.getNumber()));
					data[4].setCellValue(getTypeName(thing));
					data[5].setCellValue(getStatusName(thing));
					incident incident = null;
					if ((list.size() - 1) >= 0) {
						incident = list.get(list.size() - 1);
						data[6].setCellValue(dateFormat.format(incident.getCreatedate()));
						data[7].setCellValue(incident.getSendunit());
						data[8].setCellValue(incident.getAccepetunit());
						data[9].setCellValue(incident.getSendname());
						data[10].setCellValue(incident.getAcceptname());
						String sString = incident.getSignatures();
						String aString = incident.getSignaturea();
						if (!StringUtil.isEmpty(sString)) {
							sString = url + sString.substring(sString.lastIndexOf("/"));
							File file = new File(sString);
							if (file.exists()) {
								insertImage(workbook, patriarch2, getImageData(ImageIO.read(file)), a + 1, 11, 1);
							} else {
								data[11].setCellValue("");
							}

						} else {
							data[11].setCellValue("");
						}
						if (!StringUtil.isEmpty(aString)) {
							aString = url + aString.substring(aString.lastIndexOf("/"));
							File file = new File(aString);
							if (file.exists()) {
								insertImage(workbook, patriarch2, getImageData(ImageIO.read(file)), a + 1, 12, 1);
							} else {
								data[12].setCellValue("");
							}

						} else {
							data[12].setCellValue("");
						}
					}

					if ((list.size() - 2) >= 0) {
						incident = list.get(list.size() - 2);
						data[13].setCellValue(dateFormat.format(incident.getCreatedate()));
						data[14].setCellValue(incident.getAccepetunit());

						if (incident.getTime() == null) {
							data[15].setCellValue("");
						} else {
							data[15].setCellValue(incident.getTime());
						}
						data[16].setCellValue(incident.getSendname());
						data[17].setCellValue(incident.getAcceptname());
						String sString = incident.getSignatures();
						String aString = incident.getSignaturea();
						if (!StringUtil.isEmpty(sString)) {
							sString = url + sString.substring(sString.lastIndexOf("/"));
							File file = new File(sString);
							if (file.exists()) {
								insertImage(workbook, patriarch2, getImageData(ImageIO.read(file)), a + 1, 18, 1);
							} else {
								data[18].setCellValue("");
							}

						} else {
							data[18].setCellValue("");
						}
						if (!StringUtil.isEmpty(aString)) {
							aString = url + aString.substring(aString.lastIndexOf("/"));
							File file = new File(aString);
							if (file.exists()) {
								insertImage(workbook, patriarch2, getImageData(ImageIO.read(file)), a + 1, 19, 1);
							} else {
								data[19].setCellValue("");
							}

						} else {
							data[19].setCellValue("");
						}
					}
					if ((list.size() - 3) >= 0) {
						incident = list.get(list.size() - 3);
						data[20].setCellValue(dateFormat.format(incident.getCreatedate()));
						data[21].setCellValue(incident.getAcceptname());

						String aString = incident.getSignaturea();
						if (!StringUtil.isEmpty(aString)) {
							aString = url + aString.substring(aString.lastIndexOf("/"));
							File file = new File(aString);
							if (file.exists()) {
								insertImage(workbook, patriarch2, getImageData(ImageIO.read(file)), a + 1, 22, 1);
							} else {
								data[22].setCellValue("");
							}

						} else {
							data[22].setCellValue("");
						}
					}
					a++;
				} else if (thing.getStatus() >= 4 && thing.getStatus() < 6) {
					HSSFRow datarow = xsSheet3.createRow(b + 1);
					HSSFCell data[] = new HSSFCell[ajtitleLen3];
					for (short k = 0; k < ajtitleLen3; k++) {
						data[k] = datarow.createCell(k);
						data[k].setCellStyle(bodyStyle);
					}
					data[0].setCellValue(thing.getName());
					data[1].setCellValue(thing.getBname());
					data[2].setCellValue(thing.getBphone());
					data[3].setCellValue(String.valueOf(thing.getNumber()));
					data[4].setCellValue(getTypeName(thing));
					data[5].setCellValue(getStatusName(thing));
					incident incident = null;
					if ((list.size() - 1) >= 0) {
						incident = list.get(list.size() - 1);
						data[6].setCellValue(dateFormat.format(incident.getCreatedate()));
						data[7].setCellValue(incident.getSendunit());
						data[8].setCellValue(incident.getAccepetunit());
						data[9].setCellValue(incident.getSendname());
						data[10].setCellValue(incident.getAcceptname());
						String sString = incident.getSignatures();
						String aString = incident.getSignaturea();
						if (!StringUtil.isEmpty(sString)) {
							sString = url + sString.substring(sString.lastIndexOf("/"));
							File file = new File(sString);
							if (file.exists()) {
								insertImage(workbook, patriarch3, getImageData(ImageIO.read(file)), b + 1, 11, 1);
							} else {
								data[11].setCellValue("");
							}

						} else {
							data[11].setCellValue("");
						}
						if (!StringUtil.isEmpty(aString)) {
							aString = url + aString.substring(aString.lastIndexOf("/"));
							File file = new File(aString);
							if (file.exists()) {
								insertImage(workbook, patriarch3, getImageData(ImageIO.read(file)), b + 1, 12, 1);
							} else {
								data[12].setCellValue("");
							}

						} else {
							data[12].setCellValue("");
						}
					}

					if ((list.size() - 2) >= 0) {
						incident = list.get(list.size() - 2);
						data[13].setCellValue(dateFormat.format(incident.getCreatedate()));
						data[14].setCellValue(incident.getAccepetunit());
						if (incident.getTime() == null) {
							data[15].setCellValue("");
						} else {
							if (incident.getTime() == null) {
								data[15].setCellValue("");
							} else {
								data[15].setCellValue(incident.getTime());
							}
						}

						data[16].setCellValue(incident.getSendname());
						data[17].setCellValue(incident.getAcceptname());

						String sString = incident.getSignatures();
						String aString = incident.getSignaturea();
						if (!StringUtil.isEmpty(sString)) {
							sString = url + sString.substring(sString.lastIndexOf("/"));
							File file = new File(sString);
							if (file.exists()) {
								insertImage(workbook, patriarch3, getImageData(ImageIO.read(file)), b + 1, 18, 1);
							} else {
								data[18].setCellValue("");
							}

						} else {
							data[18].setCellValue("");
						}
						if (!StringUtil.isEmpty(aString)) {
							aString = url + aString.substring(aString.lastIndexOf("/"));
							File file = new File(aString);
							if (file.exists()) {
								insertImage(workbook, patriarch3, getImageData(ImageIO.read(file)), b + 1, 19, 1);
							} else {
								data[19].setCellValue("");
							}

						} else {
							data[19].setCellValue("");
						}
					}
					if ((list.size() - 3) >= 0) {
						incident = list.get(list.size() - 3);
						data[20].setCellValue(dateFormat.format(incident.getCreatedate()));
						data[21].setCellValue(incident.getSendname());
						data[22].setCellValue(incident.getAcceptname());

						String sString = incident.getSignatures();
						String aString = incident.getSignaturea();
						if (!StringUtil.isEmpty(sString)) {
							sString = url + sString.substring(sString.lastIndexOf("/"));
							File file = new File(sString);
							if (file.exists()) {
								insertImage(workbook, patriarch3, getImageData(ImageIO.read(file)), b + 1, 23, 1);
							} else {
								data[23].setCellValue("");
							}

						} else {
							data[23].setCellValue("");
						}
						if (!StringUtil.isEmpty(aString)) {
							aString = url + aString.substring(aString.lastIndexOf("/"));
							File file = new File(aString);
							if (file.exists()) {
								insertImage(workbook, patriarch3, getImageData(ImageIO.read(file)), b + 1, 24, 1);
							} else {
								data[24].setCellValue("");
							}

						} else {
							data[24].setCellValue("");
						}
					}
					if ((list.size() - 4) >= 0) {
						incident = list.get(list.size() - 4);
						data[25].setCellValue(dateFormat.format(incident.getCreatedate()));
						data[26].setCellValue(incident.getSendname());
						data[27].setCellValue(incident.getAcceptname());

						String sString = incident.getSignatures();
						String aString = incident.getSignaturea();
						if (!StringUtil.isEmpty(sString)) {
							sString = url + sString.substring(sString.lastIndexOf("/"));
							File file = new File(sString);
							if (file.exists()) {
								insertImage(workbook, patriarch3, getImageData(ImageIO.read(file)), b + 1, 28, 1);
							} else {
								data[28].setCellValue("");
							}

						} else {
							data[28].setCellValue("");
						}
						if (!StringUtil.isEmpty(aString)) {
							aString = url + aString.substring(aString.lastIndexOf("/"));
							File file = new File(aString);
							if (file.exists()) {
								insertImage(workbook, patriarch3, getImageData(ImageIO.read(file)), b + 1, 29, 1);
							} else {
								data[29].setCellValue("");
							}

						} else {
							data[29].setCellValue("");
						}
					}
					b++;
				} else if (thing.getStatus() >= 6 && thing.getStatus() < 8) {
					HSSFRow datarow = xsSheet4.createRow(c + 1);
					HSSFCell data[] = new HSSFCell[ajtitleLen4];
					System.out.println(ajtitleLen4);
					for (short k = 0; k < ajtitleLen4; k++) {
						data[k] = datarow.createCell(k);
						data[k].setCellStyle(bodyStyle);
					}
					data[0].setCellValue(thing.getName());
					data[1].setCellValue(thing.getBname());
					data[2].setCellValue(thing.getBphone());
					data[3].setCellValue(String.valueOf(thing.getNumber()));
					data[4].setCellValue(getTypeName(thing));
					data[5].setCellValue(getStatusName(thing));
					incident incident = null;
					if ((list.size() - 1) >= 0) {
						incident = list.get(list.size() - 1);
						data[6].setCellValue(dateFormat.format(incident.getCreatedate()));
						data[7].setCellValue(incident.getSendunit());
						data[8].setCellValue(incident.getAccepetunit());
						data[9].setCellValue(incident.getSendname());
						data[10].setCellValue(incident.getAcceptname());

						String sString = incident.getSignatures();
						String aString = incident.getSignaturea();
						if (!StringUtil.isEmpty(sString)) {
							sString = url + sString.substring(sString.lastIndexOf("/"));
							File file = new File(sString);
							if (file.exists()) {
								insertImage(workbook, patriarch4, getImageData(ImageIO.read(file)), c + 1, 11, 1);
							} else {
								data[11].setCellValue("");
							}

						} else {
							data[11].setCellValue("");
						}
						if (!StringUtil.isEmpty(aString)) {
							aString = url + aString.substring(aString.lastIndexOf("/"));
							File file = new File(aString);
							if (file.exists()) {
								insertImage(workbook, patriarch4, getImageData(ImageIO.read(file)), c + 1, 12, 1);
							} else {
								data[12].setCellValue("");
							}

						} else {
							data[12].setCellValue("");
						}
					}

					if ((list.size() - 2) >= 0) {
						incident = list.get(list.size() - 2);
						data[13].setCellValue(dateFormat.format(incident.getCreatedate()));
						data[14].setCellValue(incident.getAccepetunit());

						if (incident.getTime() == null) {
							data[15].setCellValue("");
						} else {
							data[15].setCellValue(incident.getTime());
						}
						data[16].setCellValue(incident.getSendname());
						data[17].setCellValue(incident.getAcceptname());

						String sString = incident.getSignatures();
						String aString = incident.getSignaturea();
						if (!StringUtil.isEmpty(sString)) {
							sString = url + sString.substring(sString.lastIndexOf("/"));
							File file = new File(sString);
							if (file.exists()) {
								insertImage(workbook, patriarch4, getImageData(ImageIO.read(file)), c + 1, 18, 1);
							} else {
								data[18].setCellValue("");
							}

						} else {
							data[18].setCellValue("");
						}
						if (!StringUtil.isEmpty(aString)) {
							aString = url + aString.substring(aString.lastIndexOf("/"));
							File file = new File(aString);
							if (file.exists()) {
								insertImage(workbook, patriarch4, getImageData(ImageIO.read(file)), c + 1, 19, 1);
							} else {
								data[19].setCellValue("");
							}

						} else {
							data[19].setCellValue("");
						}
					}
					if ((list.size() - 3) >= 0) {
						incident = list.get(list.size() - 3);
						data[20].setCellValue(dateFormat.format(incident.getCreatedate()));
						data[21].setCellValue(incident.getReason());
						data[22].setCellValue(incident.getSendname());
						data[23].setCellValue(incident.getAcceptname());

						String sString = incident.getSignatures();
						String aString = incident.getSignaturea();
						if (!StringUtil.isEmpty(sString)) {
							sString = url + sString.substring(sString.lastIndexOf("/"));
							File file = new File(sString);
							if (file.exists()) {
								insertImage(workbook, patriarch4, getImageData(ImageIO.read(file)), c + 1, 24, 1);
							} else {
								data[24].setCellValue("");
							}

						} else {
							data[24].setCellValue("");
						}
						if (!StringUtil.isEmpty(aString)) {
							aString = url + aString.substring(aString.lastIndexOf("/"));
							File file = new File(aString);
							if (file.exists()) {
								insertImage(workbook, patriarch4, getImageData(ImageIO.read(file)), c + 1, 25, 1);
							} else {
								data[25].setCellValue("");
							}
						} else {
							data[25].setCellValue("");
						}
					}
					if ((list.size() - 4) >= 0) {
						incident = list.get(list.size() - 4);
						data[26].setCellValue(dateFormat.format(incident.getCreatedate()));
						data[27].setCellValue(incident.getSendname());
						data[28].setCellValue(incident.getAcceptname());

						String sString = incident.getSignatures();
						String aString = incident.getSignaturea();
						if (!StringUtil.isEmpty(sString)) {
							sString = url + sString.substring(sString.lastIndexOf("/"));
							File file = new File(sString);
							if (file.exists()) {
								insertImage(workbook, patriarch4, getImageData(ImageIO.read(file)), c + 1, 29, 1);
							} else {
								data[29].setCellValue("");
							}

						} else {
							data[29].setCellValue("");
						}
						if (!StringUtil.isEmpty(aString)) {
							aString = url + aString.substring(aString.lastIndexOf("/"));
							File file = new File(aString);
							if (file.exists()) {
								insertImage(workbook, patriarch4, getImageData(ImageIO.read(file)), c + 1, 30, 1);
							} else {
								data[30].setCellValue("");
							}

						} else {
							data[30].setCellValue("");
						}
					}
					c++;
				}

			}
			if (thing.getTypeid() == 2) {
				HSSFRow datarow = xsSheet5.createRow(d + 1);
				HSSFCell data[] = new HSSFCell[ajtitleLen5];
				for (short k = 0; k < ajtitleLen5; k++) {
					data[k] = datarow.createCell(k);
					data[k].setCellStyle(bodyStyle);
				}
				data[0].setCellValue(thing.getName());
				data[1].setCellValue(String.valueOf(thing.getNumber()));
				data[2].setCellValue(getTypeName(thing));
				data[3].setCellValue(getStatusName(thing));
				incident incident = null;
				if ((list.size() - 1) >= 0) {
					incident = list.get(list.size() - 1);
					data[4].setCellValue(dateFormat.format(incident.getCreatedate()));
					data[5].setCellValue(incident.getSendunit());
					data[6].setCellValue(incident.getAccepetunit());
					data[7].setCellValue(incident.getSendname());
					data[8].setCellValue(incident.getAcceptname());

					String sString = incident.getSignatures();
					String aString = incident.getSignaturea();
					if (!StringUtil.isEmpty(sString)) {
						sString = url + sString.substring(sString.lastIndexOf("/"));
						File file = new File(sString);
						if (file.exists()) {
							insertImage(workbook, patriarch5, getImageData(ImageIO.read(file)), d + 1, 9, 1);
						} else {
							data[9].setCellValue("");
						}

					} else {
						data[9].setCellValue("");
					}
					if (!StringUtil.isEmpty(aString)) {
						System.out.println(aString);
						aString = url + aString.substring(aString.lastIndexOf("/"));
						File file = new File(aString);
						if (file.exists()) {
							insertImage(workbook, patriarch5, getImageData(ImageIO.read(file)), d + 1, 10, 1);
						} else {
							data[10].setCellValue("");
						}

					} else {
						data[10].setCellValue("");
					}
				}

				if ((list.size() - 2) >= 0) {
					incident = list.get(list.size() - 2);
					data[11].setCellValue(dateFormat.format(incident.getCreatedate()));
					data[12].setCellValue(incident.getAccepetunit());
					data[13].setCellValue(incident.getSendname());
					data[14].setCellValue(incident.getAcceptname());

					String sString = incident.getSignatures();
					String aString = incident.getSignaturea();
					if (!StringUtil.isEmpty(sString)) {
						sString = url + sString.substring(sString.lastIndexOf("/"));
						File file = new File(sString);
						if (file.exists()) {
							insertImage(workbook, patriarch5, getImageData(ImageIO.read(file)), d + 1, 15, 1);
						} else {
							data[15].setCellValue("");
						}

					} else {
						data[15].setCellValue("");
					}
					if (!StringUtil.isEmpty(aString)) {
						aString = url + aString.substring(aString.lastIndexOf("/"));
						File file = new File(aString);
						if (file.exists()) {
							insertImage(workbook, patriarch5, getImageData(ImageIO.read(file)), d + 1, 16, 1);
						} else {
							data[16].setCellValue("");
						}

					} else {
						data[16].setCellValue("");
					}
				}
				d++;
			}
			if (thing.getTypeid() == 3) {
				HSSFRow datarow = xsSheet6.createRow(e + 1);
				HSSFCell data[] = new HSSFCell[ajtitleLen6];
				for (short k = 0; k < ajtitleLen6; k++) {
					data[k] = datarow.createCell(k);
					data[k].setCellStyle(bodyStyle);
				}
				data[0].setCellValue(thing.getName());
				data[1].setCellValue(String.valueOf(thing.getNumber()));
				data[2].setCellValue(getTypeName(thing));
				data[3].setCellValue(getStatusName(thing));
				incident incident = null;
				if ((list.size() - 1) >= 0) {
					incident = list.get(list.size() - 1);
					data[4].setCellValue(dateFormat.format(incident.getCreatedate()));
					data[5].setCellValue(incident.getSendunit());
					data[6].setCellValue(incident.getAccepetunit());
					data[7].setCellValue(incident.getSendname());
					data[8].setCellValue(incident.getAcceptname());

					String sString = incident.getSignatures();
					String aString = incident.getSignaturea();
					if (!StringUtil.isEmpty(sString)) {
						sString = url + sString.substring(sString.lastIndexOf("/"));
						File file = new File(sString);
						if (file.exists()) {
							insertImage(workbook, patriarch6, getImageData(ImageIO.read(file)), e + 1, 9, 1);
						} else {
							data[9].setCellValue("");
						}

					} else {
						data[9].setCellValue("");
					}
					if (!StringUtil.isEmpty(aString)) {
						aString = url + aString.substring(aString.lastIndexOf("/"));
						File file = new File(aString);
						if (file.exists()) {
							insertImage(workbook, patriarch6, getImageData(ImageIO.read(file)), e + 1, 10, 1);
						} else {
							data[10].setCellValue("");
						}

					} else {
						data[10].setCellValue("");
					}
				}

				if ((list.size() - 2) >= 0) {
					incident = list.get(list.size() - 2);
					data[11].setCellValue(dateFormat.format(incident.getCreatedate()));
					data[12].setCellValue(incident.getAccepetunit());
					data[13].setCellValue(incident.getSendname());
					data[14].setCellValue(incident.getAcceptname());

					String sString = incident.getSignatures();
					String aString = incident.getSignaturea();
					if (!StringUtil.isEmpty(sString)) {
						sString = url + sString.substring(sString.lastIndexOf("/"));
						File file = new File(sString);
						if (file.exists()) {
							insertImage(workbook, patriarch6, getImageData(ImageIO.read(file)), e + 1, 15, 1);
						} else {
							data[15].setCellValue("");
						}

					} else {
						data[15].setCellValue("");
					}
					if (!StringUtil.isEmpty(aString)) {
						aString = url + aString.substring(aString.lastIndexOf("/"));
						File file = new File(aString);
						if (file.exists()) {
							insertImage(workbook, patriarch6, getImageData(ImageIO.read(file)), e + 1, 16, 1);
						} else {
							data[16].setCellValue("");
						}

					} else {
						data[16].setCellValue("");
					}
				}
				e++;
			}

		}
		return workbook;
	}

	private static String getStatusName(thing thing) {
		if ((thing.getTypeid() == 2 || thing.getTypeid() == 4 || thing.getTypeid() == 1) && thing.getStatus() == 1) {
			return "社区已交件";
		} else if ((thing.getTypeid() == 2 || thing.getTypeid() == 4 || thing.getTypeid() == 1)
				&& thing.getStatus() == 2) {
			return "部门已接件";
		} else if (thing.getStatus() == 3) {
			return "部门已办结";
		} else if (thing.getStatus() == 4) {
			return "返件-部门已交件";
		} else if (thing.getStatus() == 5) {
			return "返件-社区已接件";
		} else if (thing.getStatus() == 6) {
			return "退件-部门已交件";
		} else if (thing.getStatus() == 7) {
			return "退件-社区已接件";
		} else if (thing.getStatus() == 8) {
			return "部门已交件";
		} else if (thing.getStatus() == 9) {
			return "社区已接件";
		} else {
			return "";
		}
	}

	private static String getTypeName(thing thing) {
		if (thing.getTypeid() == 1) {
			return "代办";
		} else if (thing.getTypeid() == 2) {
			return "代收";
		} else if (thing.getTypeid() == 3) {
			return "代发";
		} else if (thing.getTypeid() == 4) {
			return "一体化救助平台";
		}
		return "";
	}

	/**
	 * 设置标题的样式
	 * 
	 * @param wb
	 * @return
	 */
	private static HSSFCellStyle createHeadStyle(HSSFWorkbook wb) {
		HSSFCellStyle style = wb.createCellStyle();
		style.setFillForegroundColor(IndexedColors.YELLOW.index);
		style.setFillPattern((short) 1);

		style.setAlignment((short) 2);
		style.setVerticalAlignment((short) 1);

		style.setAlignment((short) 2);
		style.setBorderBottom((short) 1);
		style.setBottomBorderColor((short) 8);
		style.setBorderLeft((short) 1);
		style.setLeftBorderColor((short) 8);
		style.setBorderRight((short) 1);
		style.setRightBorderColor((short) 8);
		style.setBorderTop((short) 1);
		style.setTopBorderColor((short) 8);

		Font font = wb.createFont();
		font.setFontHeightInPoints((short) 16);
		font.setFontName(" 宋体 ");
		font.setBoldweight((short) 700);
		style.setFont(font);
		return style;
	}

	/**
	 * 设置单元格样式<br>
	 * 
	 * @param workbook
	 *            workbook 工作薄
	 * @return HSSFCellStyle 单元格样式
	 */
	private static HSSFCellStyle createBodyStyle(HSSFWorkbook wb) {
		HSSFCellStyle style = wb.createCellStyle();
		style.setAlignment((short) 0);
		style.setVerticalAlignment((short) 1);

		style.setAlignment((short) 2);
		style.setBorderBottom((short) 1);
		style.setBottomBorderColor((short) 8);
		style.setBorderLeft((short) 1);
		style.setLeftBorderColor((short) 8);
		style.setBorderRight((short) 1);
		style.setRightBorderColor((short) 8);
		style.setBorderTop((short) 1);
		style.setTopBorderColor((short) 8);
		style.setWrapText(true);

		Font font = wb.createFont();
		font.setFontHeightInPoints((short) 12);
		font.setFontName(" 宋体 ");
		style.setFont(font);
		return style;
	}

	// 自定义的方法,插入某个图片到指定索引的位置
	private static void insertImage(HSSFWorkbook wb, HSSFPatriarch pa, byte[] data, int row, int column, int index) {
		int x1 = index * 250;
		int y1 = 0;
		int x2 = x1 + 255;
		int y2 = 255;
		HSSFClientAnchor anchor = new HSSFClientAnchor(x1, y1, x2, y2, (short) column, row, (short) column, row);
		anchor.setAnchorType(2);
		pa.createPicture(anchor, wb.addPicture(data, HSSFWorkbook.PICTURE_TYPE_JPEG));
	}

	// 从图片里面得到字节数组
	private static byte[] getImageData(BufferedImage bi) {
		try {
			// Image image = bufferedImage.getScaledInstance(width, height,
			//         Image.SCALE_DEFAULT);
			int w = 260;// (int) (bi.getWidth(null) * 0.36);
			int h = 82;// (int) (bi.getHeight(null) * 0.25);

			BufferedImage tmp = new BufferedImage(w, h, BufferedImage.TYPE_INT_ARGB);
			Graphics2D g2 = tmp.createGraphics();
			g2.setRenderingHint(RenderingHints.KEY_INTERPOLATION, RenderingHints.VALUE_INTERPOLATION_BICUBIC);
			g2.drawImage(bi.getScaledInstance(w, h, bi.SCALE_SMOOTH), 0, 0, w, h, null);
			g2.dispose();

			ByteArrayOutputStream bout = new ByteArrayOutputStream();
			ImageIO.write(tmp, "PNG", bout);
			return bout.toByteArray();
		} catch (Exception exe) {
			exe.printStackTrace();
			return null;
		}
	}
}
